﻿using SMALLERP.DataClass;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace XJLSC.FM
{
    public partial class YSZKMX : Form
    {
        private readonly DataBase db = new DataBase();
        private SqlDataReader sdr1;
        public YSZKMX()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            String strSql = "";
            if(checkBox1.CheckState==CheckState.Unchecked)
            {
                strSql = "declare @sql1 VARCHAR(8000) set @sql1 = 'select RTRIM(TA004) 客户编号,RTRIM(MA002) 客户简称,RTRIM(MV002) 业务员' select @sql1 = @sql1 + ' , sum(case TA003 when ''' + XQRQ + ''' then YSJE else 0 end) [' + XQRQ + ']' from (select DISTINCT TA003 AS XQRQ from V_YSRQ WHERE TA003>=CONVERT(CHAR(8),DATEADD(DAY,0,'" + dateTimePicker1.Value.ToShortDateString() + "'),112) AND TA003<=CONVERT(CHAR(8),DATEADD(DAY,0,'" + dateTimePicker2.Value.ToShortDateString() + "'),112)) as a set @sql1 = @sql1 + ' from V_YSXJ A LEFT JOIN COPMA ON TA004=MA001 LEFT JOIN CMSMV ON MA016=MV001 WHERE (MA001 LIKE ''%" + textBox1.Text + "%'' OR MA002 LIKE ''%" + textBox1.Text + "%'') AND (ISNULL(MV001,'''') LIKE ''%" + textBox2.Text + "%'' OR ISNULL(MV002,'''') LIKE ''%" + textBox2.Text + "%'') GROUP BY TA004,RTRIM(MA002),RTRIM(MV002) ORDER BY TA004 ASC' EXEC(@sql1)";
            }
            else
            {
                strSql = "declare @sql1 VARCHAR(8000) set @sql1 = 'select RTRIM(TA004) 客户编号,RTRIM(MA002) 客户简称,RTRIM(MV002) 业务员' select @sql1 = @sql1 + ' , sum(case TA003 when ''' + XQRQ + ''' then YSJE else 0 end) [' + XQRQ + ']' from (select DISTINCT TA003 AS XQRQ from V_YSRQ WHERE TA003>=CONVERT(CHAR(8),DATEADD(DAY,0,'" + dateTimePicker1.Value.ToShortDateString() + "'),112) AND TA003<=CONVERT(CHAR(8),DATEADD(DAY,0,'" + dateTimePicker2.Value.ToShortDateString() + "'),112)) as a set @sql1 = @sql1 + ' from V_KPXJ A LEFT JOIN COPMA ON TA004=MA001 LEFT JOIN CMSMV ON MA016=MV001 WHERE (MA001 LIKE ''%" + textBox1.Text + "%'' OR MA002 LIKE ''%" + textBox1.Text + "%'') AND (ISNULL(MV001,'''') LIKE ''%" + textBox2.Text + "%'' OR ISNULL(MV002,'''') LIKE ''%" + textBox2.Text + "%'') GROUP BY TA004,RTRIM(MA002),RTRIM(MV002) ORDER BY TA004 ASC' EXEC(@sql1)";
            }

            try
            {
                DataTable dt;
                dataGridView1.Columns.Clear();
                dt = db.GetDataSet1(strSql, "PURTD").Tables["PURTD"];
                dt.Rows.Add();
                dataGridView1.DataSource = dt;
                this.dataGridView1.AlternatingRowsDefaultCellStyle.BackColor = Color.LightGray;
                this.dataGridView1.AllowUserToAddRows = false;
                this.dataGridView1.Columns["业务员"].Frozen = true;

                int index = this.dataGridView1.Rows.Count-1;
                this.dataGridView1.Rows[index].Cells[this.dataGridView1.Columns["业务员"].Index].Value = "合计";
                this.dataGridView1.Rows[index].DefaultCellStyle.BackColor = Color.Yellow;
                for (int i = this.dataGridView1.Columns["业务员"].Index + 1; i <= dataGridView1.Columns.Count - 1; i++)
                {
                    double sum2 = 0;
                    for (int l = 0; l < this.dataGridView1.Rows.Count - 2; l++)
                    {
                        sum2 += double.Parse(this.dataGridView1.Rows[l].Cells[i].Value == null ? "0" : this.dataGridView1.Rows[l].Cells[i].Value.ToString());
                    }
                    this.dataGridView1.Rows[index].Cells[i].Value = sum2;
                }

                DataGridViewTextBoxColumn colCB = new DataGridViewTextBoxColumn();
                colCB.Name = "colCB";
                colCB.HeaderText = "小计";
                dataGridView1.Columns.Insert(dataGridView1.Columns.Count, colCB);

                //this.dataGridView1.Rows[index].Cells[this.dataGridView1.Columns["业务员"].Index].Value = "合计";
                //this.dataGridView1.Rows[index].DefaultCellStyle.BackColor = Color.Yellow;
                for (int i = 0; i <= dataGridView1.Rows.Count-1; i++)
                {
                    double sum3 = 0;
                    for (int l = this.dataGridView1.Columns["业务员"].Index + 1; l < dataGridView1.Columns.Count - 1; l++)
                    {
                        sum3 += double.Parse(this.dataGridView1.Rows[i].Cells[l].Value == null ? "0" : this.dataGridView1.Rows[i].Cells[l].Value.ToString());
                    }
                    this.dataGridView1.Rows[i].Cells["colCB"].Value = sum3;
                }

            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "软件提示");
                //throw ex;
            }
        }

        private void button2_Click(object sender, EventArgs e)
        {
            SaveFileDialog dlg = new SaveFileDialog();
            dlg.Filter = "Execl files (*.xls)|*.xls";
            dlg.FilterIndex = 0;
            dlg.RestoreDirectory = true;
            dlg.CreatePrompt = true;
            dlg.Title = "保存为Excel文件";
            if (dlg.ShowDialog() == DialogResult.OK)
            {
                Stream myStream;
                myStream = dlg.OpenFile();
                StreamWriter sw = new StreamWriter(myStream, System.Text.Encoding.GetEncoding(-0));
                string columnTitle = null;
                try
                {
                    //写入列标题    
                    for (int i = 0; i < dataGridView1.ColumnCount; i++)
                    {
                        if (dataGridView1.Columns[i].Visible == true)
                        {
                            if (i > 0)
                            {
                                columnTitle += "\t";
                            }
                            columnTitle += dataGridView1.Columns[i].HeaderText;

                        }

                    }
                    sw.WriteLine(columnTitle);
                    //写入列内容    
                    for (int j = 0; j < dataGridView1.Rows.Count; j++)
                    {

                        string columnValue = null;
                        for (int k = 0; k < dataGridView1.Columns.Count; k++)
                        {
                            if (dataGridView1.Columns[k].Visible == true)
                            {
                                if (k > 0)
                                {
                                    columnValue += "\t";
                                }
                                if (dataGridView1.Rows[j].Cells[k].Value == null)
                                    columnValue += "";
                                else
                                    columnValue += dataGridView1.Rows[j].Cells[k].Value.ToString().Trim();
                            }
                        }
                        sw.WriteLine(columnValue);
                    }
                    sw.Close();
                    myStream.Close();
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.ToString());
                }
                finally
                {
                    sw.Close();
                    myStream.Close();
                }
            }
        }

        private void button4_Click(object sender, EventArgs e)
        {
            if(textBox4.Text=="")
            {
                MessageBox.Show("必须输入客户编号","系统提示");
                return;
            }
            string strSql = null;
            int index = 0;
            strSql = "SELECT ISNULL(SUM(YSJE),0)+(SELECT ISNULL(SUM(TL020),0) FROM ACRTL LEFT JOIN ACRTK ON TL001=TK001 AND TL002=TK002 WHERE TK003>=CONVERT(CHAR(8),DATEADD(DAY,0,'" + dateTimePicker3.Value.ToShortDateString() + "'),112) AND TL009<CONVERT(CHAR(8),DATEADD(DAY,0,'" + dateTimePicker3.Value.ToShortDateString() + "'),112) AND TK004='" + textBox4.Text + "') YSXJ FROM V_YSXJ WHERE TA004='" + textBox4.Text + "' AND TA003<SUBSTRING(CONVERT(CHAR(8),DATEADD(DAY,0,'" + dateTimePicker3.Value.ToShortDateString() + "'),112),1,6)";
            try
            {
                dataGridView2.Rows.Clear();
                sdr1 = db.GetDataReader1(strSql);
                while (sdr1.Read())
                {
                    index = dataGridView2.Rows.Add();
                    this.dataGridView2.Rows[index].Cells["ZKLX"].Value = "期初余额";
                    this.dataGridView2.Rows[index].Cells["ZKJE"].Value = sdr1["YSXJ"].ToString();
                }
                if (sdr1.HasRows == false)
                {
                     index = dataGridView2.Rows.Add();
                    this.dataGridView2.Rows[index].Cells["ZKLX"].Value = "期初余额";
                    this.dataGridView2.Rows[index].Cells["ZKJE"].Value = "0";
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "软件提示");
                throw ex;
            }
            finally
            {
                sdr1.Close();
            }

            strSql = "SELECT XHLX,TG003,TH001,TH002,TH003,RTRIM(TH004) TH004,RTRIM(TH005) TH005,RTRIM(TH006) TH006,CAST(TH012 AS REAL) TH012,CAST(TH008 AS REAL) TH008,CAST(TH013 AS REAL) TH013,RTRIM(ZKFX) ZKFX,RTRIM(TG020) TG020 FROM V_YSMX WHERE TG004='" + textBox4.Text + "' AND TG003>=CONVERT(CHAR(8),DATEADD(DAY,0,'" + dateTimePicker3.Value.ToShortDateString() + "'),112) AND TG003<=CONVERT(CHAR(8),DATEADD(DAY,0,'" + dateTimePicker4.Value.ToShortDateString() + "'),112) ORDER BY TG003";
        
            try
            {
                sdr1 = db.GetDataReader1(strSql);
                while (sdr1.Read())
                {
                     index = dataGridView2.Rows.Add();
                    this.dataGridView2.Rows[index].Cells["ZKLX"].Value = sdr1["XHLX"].ToString();
                    this.dataGridView2.Rows[index].Cells["ZKRQ"].Value = sdr1["TG003"].ToString();
                    this.dataGridView2.Rows[index].Cells["ZKDB"].Value = sdr1["TH001"].ToString();
                    this.dataGridView2.Rows[index].Cells["ZKDH"].Value = sdr1["TH002"].ToString();
                    this.dataGridView2.Rows[index].Cells["ZKXH"].Value = sdr1["TH003"].ToString();
                    this.dataGridView2.Rows[index].Cells["ZKPH"].Value = sdr1["TH004"].ToString();
                    this.dataGridView2.Rows[index].Cells["ZKPM"].Value = sdr1["TH005"].ToString();
                    this.dataGridView2.Rows[index].Cells["ZKGG"].Value = sdr1["TH006"].ToString();
                    this.dataGridView2.Rows[index].Cells["ZKDJ"].Value = sdr1["TH012"].ToString();
                    this.dataGridView2.Rows[index].Cells["ZKSL"].Value = sdr1["TH008"].ToString();
                    this.dataGridView2.Rows[index].Cells["ZKJE"].Value = sdr1["TH013"].ToString();
                    this.dataGridView2.Rows[index].Cells["ZKFX"].Value = sdr1["ZKFX"].ToString();
                    this.dataGridView2.Rows[index].Cells["ZKBZ"].Value = sdr1["TG020"].ToString();
                }
                if (sdr1.HasRows == false)
                {

                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "软件提示");
                throw ex;
            }
            finally
            {
                sdr1.Close();
            }
            double qmje = Convert.ToDouble(dataGridView2.Rows[0].Cells["ZKJE"].Value.ToString());
            for(int i=1;i<=dataGridView2.Rows.Count-1;i++)
            {
                String zkfx = (dataGridView2.Rows[i].Cells["ZKFX"].Value == null) ? "" : dataGridView2.Rows[i].Cells["ZKFX"].Value.ToString();
                double zkje = (dataGridView2.Rows[i].Cells["ZKJE"].Value == null) ? 0.00 : Convert.ToDouble(dataGridView2.Rows[i].Cells["ZKJE"].Value.ToString());
                if (zkfx=="贷")
                {
                    qmje += zkje * (-1.00);
                }
                else
                {
                    qmje += zkje ;
                }
            }
              index = dataGridView2.Rows.Add();
            this.dataGridView2.Rows[index].Cells["ZKLX"].Value = "期末余额";
            this.dataGridView2.Rows[index].Cells["ZKJE"].Value = qmje.ToString();
        }

        private void YSZKMX_Load(object sender, EventArgs e)
        {
            dateTimePicker1.Value = DateTime.Now.AddDays(1 - DateTime.Now.Day).Date;
            dateTimePicker2.Value = DateTime.Now.AddDays(1 - DateTime.Now.Day).Date.AddMonths(1).AddSeconds(-1);

            dateTimePicker3.Value = DateTime.Now.AddDays(1 - DateTime.Now.Day).Date;
            dateTimePicker4.Value = DateTime.Now.AddDays(1 - DateTime.Now.Day).Date.AddMonths(1).AddSeconds(-1);
        }

        private void button3_Click(object sender, EventArgs e)
        {
            SaveFileDialog dlg = new SaveFileDialog();
            dlg.Filter = "Execl files (*.xls)|*.xls";
            dlg.FilterIndex = 0;
            dlg.RestoreDirectory = true;
            dlg.CreatePrompt = true;
            dlg.Title = "保存为Excel文件";
            if (dlg.ShowDialog() == DialogResult.OK)
            {
                Stream myStream;
                myStream = dlg.OpenFile();
                StreamWriter sw = new StreamWriter(myStream, System.Text.Encoding.GetEncoding(-0));
                string columnTitle = null;
                try
                {
                    //写入列标题    
                    for (int i = 0; i < dataGridView2.ColumnCount; i++)
                    {
                        if (dataGridView2.Columns[i].Visible == true)
                        {
                            if (i > 0)
                            {
                                columnTitle += "\t";
                            }
                            columnTitle += dataGridView2.Columns[i].HeaderText;

                        }

                    }
                    sw.WriteLine(columnTitle);
                    //写入列内容    
                    for (int j = 0; j < dataGridView2.Rows.Count; j++)
                    {

                        string columnValue = null;
                        for (int k = 0; k < dataGridView2.Columns.Count; k++)
                        {
                            if (dataGridView2.Columns[k].Visible == true)
                            {
                                if (k > 0)
                                {
                                    columnValue += "\t";
                                }
                                if (dataGridView2.Rows[j].Cells[k].Value == null)
                                    columnValue += "";
                                else
                                    columnValue += dataGridView2.Rows[j].Cells[k].Value.ToString().Trim();
                            }
                        }
                        sw.WriteLine(columnValue);
                    }
                    sw.Close();
                    myStream.Close();
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.ToString());
                }
                finally
                {
                    sw.Close();
                    myStream.Close();
                }
            }
        }

        private void button6_Click(object sender, EventArgs e)
        {
            String strSql = "";
            //strSql = "declare @sql1 VARCHAR(8000) set @sql1 = 'select RTRIM(TA004) 客户编号,RTRIM(MA002) 客户简称,RTRIM(MV002) 业务员' select @sql1 = @sql1 + ' , sum(case TA003 when ''' + XQRQ + ''' then YSJE else 0 end) [' + XQRQ + ']' from (select DISTINCT TA003 AS XQRQ from V_YSRQ WHERE TA003>=CONVERT(CHAR(8),DATEADD(DAY,0,'" + dateTimePicker1.Value.ToShortDateString() + "'),112) AND TA003<=CONVERT(CHAR(8),DATEADD(DAY,0,'" + dateTimePicker2.Value.ToShortDateString() + "'),112)) as a set @sql1 = @sql1 + ' from V_KPXJ A LEFT JOIN COPMA ON TA004=MA001 LEFT JOIN CMSMV ON MA016=MV001 WHERE (MA001 LIKE ''%" + textBox1.Text + "%'' OR MA002 LIKE ''%" + textBox1.Text + "%'') AND (ISNULL(MV001,'''') LIKE ''%" + textBox2.Text + "%'' OR ISNULL(MV002,'''') LIKE ''%" + textBox2.Text + "%'') GROUP BY TA004,RTRIM(MA002),RTRIM(MV002) ORDER BY TA004 ASC' EXEC(@sql1)";
            if(checkBox2.CheckState==CheckState.Unchecked)
            {
                strSql = "declare @sql1 VARCHAR(8000) set @sql1 = 'select RTRIM(TG004) 客户编号,RTRIM(MA002) 客户简称,RTRIM(MV002) 业务员' select @sql1 = @sql1 + ' , sum(case SUBSTRING(TG003,1,6) when ''' + XQRQ + ''' then TH013 else 0 end) [' + XQRQ + ']' from(select XQRQ from V_XSNY WHERE XQRQ >= SUBSTRING(CONVERT(CHAR(8), DATEADD(DAY, 0, '" + dateTimePicker5.Value.ToShortDateString() + "'), 112),1,6) AND XQRQ <= CONVERT(CHAR(8), DATEADD(DAY, 0, '" + dateTimePicker6.Value.ToShortDateString() + "'), 112)) as a set @sql1 = @sql1 + ' from V_COPTH LEFT JOIN COPMA ON TG004=MA001 LEFT JOIN CMSMV ON MA016=MV001 WHERE (ISNULL(MV001,'''') LIKE ''%" + textBox3.Text + "%'' OR ISNULL(MV002,'''') LIKE ''%" + textBox3.Text + "%'')  GROUP BY TG004,RTRIM(MA002),RTRIM(MV002) ORDER BY TG004 ASC' EXEC(@sql1)";
            }
            else
            {
                strSql = "declare @sql1 VARCHAR(8000) set @sql1 = 'select RTRIM(TI004) 客户编号,RTRIM(MA002) 客户简称,RTRIM(MV002) 业务员' select @sql1 = @sql1 + ' , sum(case SUBSTRING(TI003,1,6) when ''' + XQRQ + ''' then TI016 else 0 end) [' + XQRQ + ']' from(select XQRQ from V_XSNY WHERE XQRQ >= SUBSTRING(CONVERT(CHAR(8), DATEADD(DAY, 0, '" + dateTimePicker5.Value.ToShortDateString() + "'), 112),1,6) AND XQRQ <= CONVERT(CHAR(8), DATEADD(DAY, 0, '" + dateTimePicker6.Value.ToShortDateString() + "'), 112)) as a set @sql1 = @sql1 + ' from ACRTI LEFT JOIN ACRTJ ON TI001=TJ001 AND TI002=TJ002 LEFT JOIN COPMA ON TI004=MA001 LEFT JOIN CMSMV ON MA016=MV001 WHERE (ISNULL(MV001,'''') LIKE ''%" + textBox3.Text + "%'' OR ISNULL(MV002,'''') LIKE ''%" + textBox3.Text + "%'')  GROUP BY TI004,RTRIM(MA002),RTRIM(MV002) ORDER BY TI004 ASC' EXEC(@sql1)";
            }

            try
            {
                DataTable dt;
                 dt = db.GetDataSet1(strSql, "COPTH").Tables["COPTH"];
                dataGridView3.Columns.Clear();
                dt.Rows.Add();
                dataGridView3.DataSource = dt;
                this.dataGridView3.AlternatingRowsDefaultCellStyle.BackColor = Color.LightGray;
                this.dataGridView3.AllowUserToAddRows = false;
                this.dataGridView3.Columns["业务员"].Frozen = true;

                int index = this.dataGridView3.Rows.Count - 1;
                this.dataGridView3.Rows[index].Cells[this.dataGridView3.Columns["业务员"].Index].Value = "合计";
                this.dataGridView3.Rows[index].DefaultCellStyle.BackColor = Color.Yellow;
                for (int i = this.dataGridView3.Columns["业务员"].Index + 1; i <= dataGridView3.Columns.Count - 1; i++)
                {
                    double sum2 = 0;
                    for (int l = 0; l< this.dataGridView3.Rows.Count - 2; l++)
                    {
                        sum2 += double.Parse(this.dataGridView3.Rows[l].Cells[i].Value == null ? "0" : this.dataGridView3.Rows[l].Cells[i].Value.ToString());
    }
                    this.dataGridView3.Rows[index].Cells[i].Value = sum2;
                }

                DataGridViewTextBoxColumn colCB = new DataGridViewTextBoxColumn();
                colCB.Name = "colCB";
                colCB.HeaderText = "小计";
                dataGridView3.Columns.Insert(dataGridView3.Columns.Count, colCB);

                //this.dataGridView1.Rows[index].Cells[this.dataGridView1.Columns["业务员"].Index].Value = "合计";
                //this.dataGridView1.Rows[index].DefaultCellStyle.BackColor = Color.Yellow;
                for (int i = 0; i <= dataGridView3.Rows.Count-1; i++)
                {
                    double sum3 = 0;
                    for (int l = this.dataGridView3.Columns["业务员"].Index + 1; l<dataGridView3.Columns.Count - 1; l++)
                    {
                      sum3 += double.Parse(this.dataGridView3.Rows[i].Cells[l].Value == null ? "0" : this.dataGridView3.Rows[i].Cells[l].Value.ToString());
                    }
                    this.dataGridView3.Rows [i].Cells ["colCB"].Value = sum3;
                }

            }
            catch (Exception ex)
            {
              MessageBox.Show(ex.Message, "软件提示");
               //throw ex;
            }
        }
    }
}
